﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using Microsoft.Win32;
using System.Windows;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Security.AccessControl;
using System.IO;

namespace TinyCinema.Data
{
       
    public class Execution:INotifyPropertyChanged
    {
        static String strConnectionToSever;
        static String strConnectionToDatabase;
        static String strSeverInstace;
        static String strUsername;
        static String strPassword;
        protected static SqlConnection sqlConnection= new SqlConnection();
        public static DataSet ds = new DataSet("TinyCinemaDataSet");
        public static void SetConnectionProperty(string instance, string username, string password)
        {
            strSeverInstace = instance;
            strUsername = username;
            strPassword = password;
            if (username==null||password==null)
            {
                strConnectionToSever = @"Server="+instance+"; Trusted_Connection=True;";
                strConnectionToDatabase = @"Server=" + instance + "; Database=TinyCinema; Trusted_Connection=True;";
            }
            else
            {
                strConnectionToSever = @"Server=" + instance + "; user id="+username+"; password="+password+"; Trusted_Connection=True;";
                strConnectionToDatabase = @"Server=" + instance + "; user id=" + username + "; password=" + password + "; Database=TinyCinema; Trusted_Connection=True;";
            }
            sqlConnection.ConnectionString = strConnectionToDatabase;
        }
        public static bool CheckConnection()
        {
            try
            {
                var Con = new ServerConnection();
                Con.ServerInstance = strSeverInstace;
                if(strUsername!=null&&strPassword!=null)
                {
                    Con.ConnectAsUser = true;
                    Con.ConnectAsUserName = strUsername;
                    Con.ConnectAsUserPassword = strPassword;
                }
                Con.Connect();
                Con.ForceDisconnected();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool isTableExist(String tableName)
        {
            if (ds.Tables.IndexOf(tableName) == -1) return false;
            return true;
        }
        public static void PutTableIntoDataSet(string TableName)
        {
            if (!isTableExist(TableName))
            {
                var sql = "Select * from " + TableName;
                SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConnection);
                if (sqlConnection.State==ConnectionState.Closed) sqlConnection.Open();
                adapter.Fill(ds, TableName);
                sqlConnection.Close();
                ds.AcceptChanges();
            }            
        }
        public static void PutNewTableIntoDataSet(string TableName)
        {
            if (IsBackingUp) return;
            if (ds.Tables.IndexOf(TableName) != -1) ds.Tables.Remove(TableName);
            var sql = "Select * from " + TableName;
            SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlConnection);
            if (sqlConnection.State == ConnectionState.Closed) sqlConnection.Open();
            try { adapter.Fill(ds, TableName); }
            catch { }
            sqlConnection.Close();
            ds.AcceptChanges();
        }
        public static string IDGenerate(string TableName)
        {
            PutTableIntoDataSet(TableName);
            var Temp = ds.Tables[TableName].Select();
            var Rows = ds.Tables[TableName].Select();
            int MaxID = 0;
            foreach(var Row in Rows)
                if (int.Parse((Row[0] as string).Substring(2,6))>MaxID)
                    MaxID = int.Parse((Row[0] as string).Substring(2,6));
            var prefix = "";

            if (TableName == Tables.Acting) prefix = Prefix.Acting;
            else if (TableName == Tables.Actor) prefix = Prefix.Actor;
            else if (TableName == Tables.BookedTicket) prefix = Prefix.BookedTicket;
            else if (TableName == Tables.Country) prefix = Prefix.Country;
            else if (TableName == Tables.Customer) prefix = Prefix.Customer;
            else if (TableName == Tables.CustomerType) prefix = Prefix.CustomerType;
            else if (TableName == Tables.Department) prefix = Prefix.Department;
            else if (TableName == Tables.Desk) prefix = Prefix.Desk;
            else if (TableName == Tables.DeskType) prefix = Prefix.DeskType;
            else if (TableName == Tables.Employee) prefix = Prefix.Employee;
            else if (TableName == Tables.Genre) prefix = Prefix.Genre;
            else if (TableName == Tables.Menu) prefix = Prefix.Menu;
            else if (TableName == Tables.MenuOrder) prefix = Prefix.MenuOrder;
            else if (TableName == Tables.MenuOrderDetail) prefix = Prefix.MenuOrderDetail;
            else if (TableName == Tables.Movie) prefix = Prefix.Movie;
            else if (TableName == Tables.OfGenre) prefix = Prefix.OfGenre;
            else if (TableName == Tables.Position) prefix = Prefix.Position;
            else if (TableName == Tables.Promotion) prefix = Prefix.Promotion;
            else if (TableName == Tables.PromotionDetail) prefix = Prefix.PromotionDetail;
            else if (TableName == Tables.Room) prefix = Prefix.Room;
            else if (TableName == Tables.Schedule) prefix = Prefix.Schedule;
            else if (TableName == Tables.ScheduleType) prefix = Prefix.ScheduleType;
            else if (TableName == Tables.ScheduleTypePrice) prefix = Prefix.ScheduleTypePrice;
            else if (TableName == Tables.Ticket) prefix = Prefix.Ticket;
            else if (TableName == Tables.TicketOrder) prefix = Prefix.TicketOrder;                    
            
            var value = MaxID+1;
            string temp = value + "";
            for (int i = temp.Length; i < 6; ++i)
                temp = "0" + temp;
            return prefix+temp;
        }        
        public static void Restore()
        {
            ds.Reset();
        }
        public static void SaveCommand(string TableName)
        {
            SqlDataAdapter adapter = new SqlDataAdapter("Select * From " + TableName, sqlConnection);
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
            try { sqlConnection.Open(); }
            catch { }
            try { adapter.Update(ds.Tables[TableName]); }
            catch { }
            sqlConnection.Close();
            ds.AcceptChanges();
        }

        public event PropertyChangedEventHandler PropertyChanged;
        protected virtual void OnPropertyChanged(string propertyName)
        {
            PropertyChangedEventHandler handler = PropertyChanged;
            if (handler != null)
                handler(this, new PropertyChangedEventArgs(propertyName));
        }

        public static DataTable Excute(string Sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(Sql, sqlConnection);
            try { sqlConnection.Open(); }
            catch { }
            DataTable dataTable = new DataTable();
            try { adapter.Fill(dataTable); }
            catch { }
            sqlConnection.Close();
            return dataTable;
        }
        public static void BackupDatabase(string backUpFile)
        {
            var Con = new ServerConnection();
            Con.ServerInstance = strSeverInstace;
            if (strUsername != null && strPassword != null)
            {
                Con.ConnectAsUser = true;
                Con.ConnectAsUserName = strUsername;
                Con.ConnectAsUserPassword = strPassword;
            }
            Server server = new Server(Con);
            Backup source = new Backup();
            source.Action = BackupActionType.Database;
            source.Database = "TinyCinema";
            BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
            source.Devices.Add(destination);
            try { source.SqlBackup(server); }
            catch
            {
                throw new System.IO.IOException();
            }
            Con.Disconnect();
        }
        static bool IsBackingUp = false;
        public static void RestoreDatabase(string backUpFile)
        {
            IsBackingUp = true;
            var Con = new ServerConnection();
            Con.ServerInstance = strSeverInstace;
            if (strUsername != null && strPassword != null)
            {
                Con.ConnectAsUser = true;
                Con.ConnectAsUserName = strUsername;
                Con.ConnectAsUserPassword = strPassword;
                
            }
            Server server = new Server(Con);
            Restore destination = new Restore();
            destination.Action = RestoreActionType.Database;
            destination.Database = "TinyCinema";
            BackupDeviceItem source;

            
            try { 
                source = new BackupDeviceItem(backUpFile, DeviceType.File);
                destination.Devices.Add(source);
                destination.ReplaceDatabase = true;
            }
            catch
            {
                throw new System.IO.FileNotFoundException();
            }
            SqlConnection Connection = new SqlConnection(strConnectionToSever);
            try
            {
                Connection.Open();
                (new SqlCommand("use Master ALTER DATABASE TinyCinema SET OFFLINE WITH ROLLBACK IMMEDIATE", Connection)).ExecuteNonQuery();
                Connection.Close();
            }
            catch { }

            try { destination.SqlRestore(server); }
            catch
            {
                try { Connection.Open(); }
                catch { }
                (new SqlCommand("use Master ALTER DATABASE TinyCinema SET ONLINE WITH ROLLBACK IMMEDIATE", Connection)).ExecuteNonQuery();
                Connection.Close();
                throw new Exception();
            }

            try { Connection.Open(); }
            catch { }
            (new SqlCommand("use Master ALTER DATABASE TinyCinema SET ONLINE WITH ROLLBACK IMMEDIATE", Connection)).ExecuteNonQuery();
            Connection.Close();

        }
    }
}
